﻿using System;
using System.Data;
using System.Text;
using System.Data.SqlClient;
using Maticsoft.DBUtility;
using System.Collections;
using System.Collections.Generic;

namespace EnterpriseFund.DAL
{
    public partial class YGZAllowance
    {
        public YGZAllowance()
        { }

        #region  BasicMethod

        /// <summary>
        /// 是否存在该记录
        /// </summary>
        public bool Exists(string FYGZAllowanceNo)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from fund.t_YGZAllowance");
            strSql.Append(" where FYGZAllowanceNo=@FYGZAllowanceNo ");
            SqlParameter[] parameters = {
					new SqlParameter("@FYGZAllowanceNo", SqlDbType.NVarChar,50)			};
            parameters[0].Value = FYGZAllowanceNo;

            return DbHelperSQL.Exists(strSql.ToString(), parameters);
        }
        /// <summary>
        /// 是否存在该记录
        /// </summary>
        public bool Exists(decimal FYear, decimal NSRDZDAH, int FPeriod)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from fund.t_YGZAllowance");
            strSql.Append(" where FYear=@FYear AND NSRDZDAH=@NSRDZDAH AND FPeriod=@FPeriod");
            SqlParameter[] parameters = {
					new SqlParameter("@FYear", SqlDbType.Decimal),	
					new SqlParameter("@NSRDZDAH", SqlDbType.Decimal),
					new SqlParameter("@FPeriod", SqlDbType.Int)			
                                        };
            parameters[0].Value = FYear;
            parameters[1].Value = NSRDZDAH;
            parameters[2].Value = FPeriod;

            return DbHelperSQL.Exists(strSql.ToString(), parameters);
        }


        /// <summary>
        /// 增加一条数据
        /// </summary>
        public bool Add(EnterpriseFund.Model.YGZAllowance model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into fund.t_YGZAllowance(");
            strSql.Append("FYGZAllowanceNo,FEntityNo,FYear,NSRDZDAH,FAmount,FExcelFileName,FExcelRowNumber,FInsEmpNo,FInsDeptNo,FInsDate,FInsIP,FUpdEmplNo,FUpdDeptNo,FUpdDate,FUpdIP,FPeriod)");
            strSql.Append(" values (");
            strSql.Append("@FYGZAllowanceNo,@FEntityNo,@FYear,@NSRDZDAH,@FAmount,@FExcelFileName,@FExcelRowNumber,@FInsEmpNo,@FInsDeptNo,@FInsDate,@FInsIP,@FUpdEmplNo,@FUpdDeptNo,@FUpdDate,@FUpdIP,@FPeriod)");
            SqlParameter[] parameters = {
					new SqlParameter("@FYGZAllowanceNo", SqlDbType.NVarChar,50),
					new SqlParameter("@FEntityNo", SqlDbType.NVarChar,12),
					new SqlParameter("@FYear", SqlDbType.Decimal,5),
					new SqlParameter("@NSRDZDAH", SqlDbType.Decimal,13),
					new SqlParameter("@FAmount", SqlDbType.Decimal,9),
					new SqlParameter("@FExcelFileName", SqlDbType.NVarChar,255),
					new SqlParameter("@FExcelRowNumber", SqlDbType.Decimal,9),
					new SqlParameter("@FInsEmpNo", SqlDbType.NVarChar,15),
					new SqlParameter("@FInsDeptNo", SqlDbType.NVarChar,12),
					new SqlParameter("@FInsDate", SqlDbType.DateTime),
					new SqlParameter("@FInsIP", SqlDbType.NVarChar,15),
					new SqlParameter("@FUpdEmplNo", SqlDbType.NVarChar,15),
					new SqlParameter("@FUpdDeptNo", SqlDbType.NVarChar,12),
					new SqlParameter("@FUpdDate", SqlDbType.DateTime),
					new SqlParameter("@FUpdIP", SqlDbType.VarChar,15),
					new SqlParameter("@FPeriod", SqlDbType.Int)};
            parameters[0].Value = model.FYGZAllowanceNo;
            parameters[1].Value = model.FEntityNo;
            parameters[2].Value = model.FYear;
            parameters[3].Value = model.NSRDZDAH;
            parameters[4].Value = model.FAmount;
            parameters[5].Value = model.FExcelFileName;
            parameters[6].Value = model.FExcelRowNumber;
            parameters[7].Value = model.FInsEmpNo;
            parameters[8].Value = model.FInsDeptNo;
            parameters[9].Value = model.FInsDate;
            parameters[10].Value = model.FInsIP;
            parameters[11].Value = model.FUpdEmplNo;
            parameters[12].Value = model.FUpdDeptNo;
            parameters[13].Value = model.FUpdDate;
            parameters[14].Value = model.FUpdIP;
            parameters[15].Value = model.FPeriod;

            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        /// <summary>
        /// 更新一条数据
        /// </summary>
        public bool Update(EnterpriseFund.Model.YGZAllowance model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update fund.t_YGZAllowance set ");
            strSql.Append("FYear=@FYear,");
            strSql.Append("FAmount=@FAmount,");
            strSql.Append("FExcelFileName=@FExcelFileName,");
            strSql.Append("FExcelRowNumber=@FExcelRowNumber,");
            strSql.Append("FInsEmpNo=@FInsEmpNo,");
            strSql.Append("FInsDeptNo=@FInsDeptNo,");
            strSql.Append("FInsDate=@FInsDate,");
            strSql.Append("FInsIP=@FInsIP,");
            strSql.Append("FUpdEmplNo=@FUpdEmplNo,");
            strSql.Append("FUpdDeptNo=@FUpdDeptNo,");
            strSql.Append("FUpdDate=@FUpdDate,");
            strSql.Append("FUpdIP=@FUpdIP,");
            strSql.Append("FPeriod=@FPeriod");
            strSql.Append(" where FYGZAllowanceNo=@FYGZAllowanceNo ");
            SqlParameter[] parameters = {
					new SqlParameter("@FYear", SqlDbType.Decimal,5),
					new SqlParameter("@FAmount", SqlDbType.Decimal,9),
					new SqlParameter("@FExcelFileName", SqlDbType.NVarChar,255),
					new SqlParameter("@FExcelRowNumber", SqlDbType.Decimal,9),
					new SqlParameter("@FInsEmpNo", SqlDbType.NVarChar,15),
					new SqlParameter("@FInsDeptNo", SqlDbType.NVarChar,12),
					new SqlParameter("@FInsDate", SqlDbType.DateTime),
					new SqlParameter("@FInsIP", SqlDbType.NVarChar,15),
					new SqlParameter("@FUpdEmplNo", SqlDbType.NVarChar,15),
					new SqlParameter("@FUpdDeptNo", SqlDbType.NVarChar,12),
					new SqlParameter("@FUpdDate", SqlDbType.DateTime),
					new SqlParameter("@FUpdIP", SqlDbType.VarChar,15),
					new SqlParameter("@FYGZAllowanceNo", SqlDbType.NVarChar,50),
					new SqlParameter("@FEntityNo", SqlDbType.NVarChar,12),
					new SqlParameter("@NSRDZDAH", SqlDbType.Decimal,13),
					new SqlParameter("@FPeriod", SqlDbType.Int)};
            parameters[0].Value = model.FYear;
            parameters[1].Value = model.FAmount;
            parameters[2].Value = model.FExcelFileName;
            parameters[3].Value = model.FExcelRowNumber;
            parameters[4].Value = model.FInsEmpNo;
            parameters[5].Value = model.FInsDeptNo;
            parameters[6].Value = model.FInsDate;
            parameters[7].Value = model.FInsIP;
            parameters[8].Value = model.FUpdEmplNo;
            parameters[9].Value = model.FUpdDeptNo;
            parameters[10].Value = model.FUpdDate;
            parameters[11].Value = model.FUpdIP;
            parameters[12].Value = model.FYGZAllowanceNo;
            parameters[13].Value = model.FEntityNo;
            parameters[14].Value = model.NSRDZDAH;
            parameters[15].Value = model.FPeriod;

            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 删除一条数据
        /// </summary>
        public bool Delete(string FYGZAllowanceNo)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from fund.t_YGZAllowance ");
            strSql.Append(" where FYGZAllowanceNo=@FYGZAllowanceNo ");
            SqlParameter[] parameters = {
					new SqlParameter("@FYGZAllowanceNo", SqlDbType.NVarChar,50)			};
            parameters[0].Value = FYGZAllowanceNo;

            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        /// <summary>
        /// 批量删除数据
        /// </summary>
        public bool DeleteList(string FYGZAllowanceNolist)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from fund.t_YGZAllowance ");
            strSql.Append(" where FYGZAllowanceNo in (" + FYGZAllowanceNolist + ")  ");
            int rows = DbHelperSQL.ExecuteSql(strSql.ToString());
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }


        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public EnterpriseFund.Model.YGZAllowance GetModel(string FYGZAllowanceNo)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("select  top 1 FYGZAllowanceNo,FEntityNo,FYear,NSRDZDAH,FAmount,FExcelFileName,FExcelRowNumber,FInsEmpNo,FInsDeptNo,FInsDate,FInsIP,FUpdEmplNo,FUpdDeptNo,FUpdDate,FUpdIP,FPeriod from fund.t_YGZAllowance ");
            strSql.Append(" where FYGZAllowanceNo=@FYGZAllowanceNo ");
            SqlParameter[] parameters = {
					new SqlParameter("@FYGZAllowanceNo", SqlDbType.NVarChar,50)			};
            parameters[0].Value = FYGZAllowanceNo;

            EnterpriseFund.Model.YGZAllowance model = new EnterpriseFund.Model.YGZAllowance();
            DataSet ds = DbHelperSQL.Query(strSql.ToString(), parameters);
            if (ds.Tables[0].Rows.Count > 0)
            {
                return DataRowToModel(ds.Tables[0].Rows[0]);
            }
            else
            {
                return null;
            }
        }


        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public EnterpriseFund.Model.YGZAllowance DataRowToModel(DataRow row)
        {
            EnterpriseFund.Model.YGZAllowance model = new EnterpriseFund.Model.YGZAllowance();
            if (row != null)
            {
                if (row["FYGZAllowanceNo"] != null)
                {
                    model.FYGZAllowanceNo = row["FYGZAllowanceNo"].ToString();
                }
                if (row["FEntityNo"] != null)
                {
                    model.FEntityNo = row["FEntityNo"].ToString();
                }
                if (row["FYear"] != null && row["FYear"].ToString() != "")
                {
                    model.FYear = decimal.Parse(row["FYear"].ToString());
                }
                if (row["NSRDZDAH"] != null && row["NSRDZDAH"].ToString() != "")
                {
                    model.NSRDZDAH = decimal.Parse(row["NSRDZDAH"].ToString());
                }
                if (row["FAmount"] != null && row["FAmount"].ToString() != "")
                {
                    model.FAmount = decimal.Parse(row["FAmount"].ToString());
                }
                if (row["FExcelFileName"] != null)
                {
                    model.FExcelFileName = row["FExcelFileName"].ToString();
                }
                if (row["FExcelRowNumber"] != null && row["FExcelRowNumber"].ToString() != "")
                {
                    model.FExcelRowNumber = decimal.Parse(row["FExcelRowNumber"].ToString());
                }
                if (row["FInsEmpNo"] != null)
                {
                    model.FInsEmpNo = row["FInsEmpNo"].ToString();
                }
                if (row["FInsDeptNo"] != null)
                {
                    model.FInsDeptNo = row["FInsDeptNo"].ToString();
                }
                if (row["FInsDate"] != null && row["FInsDate"].ToString() != "")
                {
                    model.FInsDate = DateTime.Parse(row["FInsDate"].ToString());
                }
                if (row["FInsIP"] != null)
                {
                    model.FInsIP = row["FInsIP"].ToString();
                }
                if (row["FUpdEmplNo"] != null)
                {
                    model.FUpdEmplNo = row["FUpdEmplNo"].ToString();
                }
                if (row["FUpdDeptNo"] != null)
                {
                    model.FUpdDeptNo = row["FUpdDeptNo"].ToString();
                }
                if (row["FUpdDate"] != null)
                {
                    model.FUpdDate = DateTime.Parse(row["FUpdDate"].ToString());
                }
                if (row["FUpdIP"] != null)
                {
                    model.FUpdIP = row["FUpdIP"].ToString();
                }
                if (row["FPeriod"] != null)
                {
                    model.FPeriod = int.Parse(row["FPeriod"].ToString());
                }
            }
            return model;
        }

        /// <summary>
        /// 获得数据列表
        /// </summary>
        public DataSet GetList(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select FYGZAllowanceNo,FEntityNo,FYear,NSRDZDAH,FAmount,FExcelFileName,FExcelRowNumber,FInsEmpNo,FInsDeptNo,FInsDate,FInsIP,FUpdEmplNo,FUpdDeptNo,FUpdDate,FUpdIP,FPeriod ");
            strSql.Append(" FROM fund.t_YGZAllowance ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            return DbHelperSQL.Query(strSql.ToString());
        }
        /// <summary>
        /// 获得数据列表
        /// </summary>
        public DataTable GetTable(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append(@"SELECT [FYGZAllowanceNo]
                          ,a.[FEntityNo]
                          ,a.[FYear]
                          ,a.[NSRDZDAH]
                          ,b.NSRSBH
                          ,b.NSRBM
                          ,b.NSRMC
                          ,c.FEmpName
                          ,[FAmount]
                          ,[FExcelFileName]
                          ,[FExcelRowNumber]
                          ,[FInsEmpNo]
                          ,[FInsDeptNo]
                          ,[FInsDate]
                          ,[FInsIP]
                          ,[FUpdEmplNo]
                          ,[FUpdDeptNo]
                          ,[FUpdDate]
                          ,[FUpdIP]
                          ,[FPeriod]
                      FROM [fund].[t_YGZAllowance] a
                      INNER JOIN [fund].[V_DJ_NSRXX] b on a.NSRDZDAH = b.NSRDZDAH
                      inner join dbo.t_HREmpl c on a.FInsEmpNo = c.FEmplNo"
                );
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            return DbHelperSQL.Query(strSql.ToString()).Tables[0];
        }

        /// <summary>
        /// 获得前几行数据
        /// </summary>
        public DataSet GetList(int Top, string strWhere, string filedOrder)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select ");
            if (Top > 0)
            {
                strSql.Append(" top " + Top.ToString());
            }
            strSql.Append(" FYGZAllowanceNo,FEntityNo,FYear,NSRDZDAH,FAmount,FExcelFileName,FExcelRowNumber,FInsEmpNo,FInsDeptNo,FInsDate,FInsIP,FUpdEmplNo,FUpdDeptNo,FUpdDate,FUpdIP,FPeriod ");
            strSql.Append(" FROM fund.t_YGZAllowance ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            strSql.Append(" order by " + filedOrder);
            return DbHelperSQL.Query(strSql.ToString());
        }

        /// <summary>
        /// 获取记录总数
        /// </summary>
        public int GetRecordCount(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) FROM fund.t_YGZAllowance ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            object obj = DbHelperSQL.GetSingle(strSql.ToString());
            if (obj == null)
            {
                return 0;
            }
            else
            {
                return Convert.ToInt32(obj);
            }
        }
        /// <summary>
        /// 分页获取数据列表
        /// </summary>
        public DataSet GetListByPage(string strWhere, string orderby, int startIndex, int endIndex)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT * FROM ( ");
            strSql.Append(" SELECT ROW_NUMBER() OVER (");
            if (!string.IsNullOrEmpty(orderby.Trim()))
            {
                strSql.Append("order by T." + orderby);
            }
            else
            {
                strSql.Append("order by T.FYGZAllowanceNo desc");
            }
            strSql.Append(")AS Row, T.*  from fund.t_YGZAllowance T ");
            if (!string.IsNullOrEmpty(strWhere.Trim()))
            {
                strSql.Append(" WHERE " + strWhere);
            }
            strSql.Append(" ) TT");
            strSql.AppendFormat(" WHERE TT.Row between {0} and {1}", startIndex, endIndex);
            return DbHelperSQL.Query(strSql.ToString());
        }

        public void ExecuteSqlTran(Hashtable SQLStringList)
        {
            DbHelperSQL.ExecuteSqlTran(SQLStringList);
        }
        
        #endregion  BasicMethod
        #region  ExtensionMethod


        /// <summary>
        /// 增加一条数据的SQL命令
        /// </summary>
        public CommandInfo AddSQL(EnterpriseFund.Model.YGZAllowance model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into fund.t_YGZAllowance(");
            strSql.Append("FYGZAllowanceNo,FEntityNo,FYear,NSRDZDAH,FAmount,FExcelFileName,FExcelRowNumber,FInsEmpNo,FInsDeptNo,FInsDate,FInsIP,FUpdEmplNo,FUpdDeptNo,FUpdDate,FUpdIP,FPeriod)");
            strSql.Append(" values (");
            strSql.Append("@FYGZAllowanceNo,@FEntityNo,@FYear,@NSRDZDAH,@FAmount,@FExcelFileName,@FExcelRowNumber,@FInsEmpNo,@FInsDeptNo,@FInsDate,@FInsIP,@FUpdEmplNo,@FUpdDeptNo,@FUpdDate,@FUpdIP,@FPeriod)");
            SqlParameter[] parameters = {
					new SqlParameter("@FYGZAllowanceNo", SqlDbType.NVarChar,50),
					new SqlParameter("@FEntityNo", SqlDbType.NVarChar,12),
					new SqlParameter("@FYear", SqlDbType.Decimal,5),
					new SqlParameter("@NSRDZDAH", SqlDbType.Decimal,13),
					new SqlParameter("@FAmount", SqlDbType.Decimal,9),
					new SqlParameter("@FExcelFileName", SqlDbType.NVarChar,255),
					new SqlParameter("@FExcelRowNumber", SqlDbType.Decimal,9),
					new SqlParameter("@FInsEmpNo", SqlDbType.NVarChar,15),
					new SqlParameter("@FInsDeptNo", SqlDbType.NVarChar,12),
					new SqlParameter("@FInsDate", SqlDbType.DateTime),
					new SqlParameter("@FInsIP", SqlDbType.NVarChar,15),
					new SqlParameter("@FUpdEmplNo", SqlDbType.NVarChar,15),
					new SqlParameter("@FUpdDeptNo", SqlDbType.NVarChar,12),
					new SqlParameter("@FUpdDate", SqlDbType.DateTime),
					new SqlParameter("@FUpdIP", SqlDbType.VarChar,15),
					new SqlParameter("@FPeriod", SqlDbType.Int)};
            parameters[0].Value = model.FYGZAllowanceNo;
            parameters[1].Value = model.FEntityNo;
            parameters[2].Value = model.FYear;
            parameters[3].Value = model.NSRDZDAH;
            parameters[4].Value = model.FAmount;
            parameters[5].Value = model.FExcelFileName;
            parameters[6].Value = model.FExcelRowNumber;
            parameters[7].Value = model.FInsEmpNo;
            parameters[8].Value = model.FInsDeptNo;
            parameters[9].Value = model.FInsDate;
            parameters[10].Value = model.FInsIP;
            parameters[11].Value = model.FUpdEmplNo;
            parameters[12].Value = model.FUpdDeptNo;
            parameters[13].Value = model.FUpdDate;
            parameters[14].Value = model.FUpdIP;
            parameters[15].Value = model.FPeriod;

            return new CommandInfo(strSql.ToString(), parameters);
        }

        /// <summary>
        /// 增加一条数据
        /// </summary>
        public bool AddList(List<EnterpriseFund.Model.YGZAllowance> models)
        {
            var cmds = new List<CommandInfo>();
            foreach(var m in models)
            {
                cmds.Add(AddSQL(m));
            }
            int rows = DbHelperSQL.ExecuteSqlTran(cmds);
            return rows > 0;
        }



        #endregion  ExtensionMethod


        public bool Delete(decimal year, int period)
        {
            var sql = string.Format("delete from fund.t_YGZAllowance where FYear = {0} and FPeriod = {1} ",year,period);
            int rows = DbHelperSQL.ExecuteSql(sql);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
    }
}

